package com.codingfly.core.jdbc;

import cn.hutool.core.convert.Convert;
import com.codingfly.core.jdbc.sql.PageSql;
import com.codingfly.core.jdbc.sql.SqlDialect;
import com.codingfly.core.jdbc.sql.SqlUtils;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Select;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;

import javax.annotation.PostConstruct;
import java.util.*;
import java.util.stream.Collectors;

@Component
public class JdbcTemplateUtil {

    public Map<String, SqlDialect> sqlDialectMap = new HashMap() {{
        put("com.mysql.jdbc.Driver", SqlDialect.MySql);
        put("com.mysql.cj.jdbc.Driver", SqlDialect.MySql);
        put("org.postgresql.Driver", SqlDialect.PostgreSQL);
        put("oracle.jdbc.driver.OracleDriver", SqlDialect.Oracle);
        put("et.sourceforge.jtds.jdbc.Driver", SqlDialect.SqlServer);
        put("com.microsoft.sqlserver.jdbc.SQLServerDriver", SqlDialect.SqlServer);
        put("org.h2.Driver", SqlDialect.H2);
    }};

    private SqlDialect sqlDialect;

    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Autowired
    public NamedParameterJdbcTemplate namedJdbcTemplate;

    private PageSql pageSql = new PageSql();

    @PostConstruct
    public void init() {
        String driverClassName = dataSourceProperties.getDriverClassName();
        sqlDialect = sqlDialectMap.get(driverClassName);
        if (sqlDialect == null) {
            throw new RuntimeException("JdbcTemplateUtil工具类不支持"+driverClassName+"自动分页");
        }
    }

    public Page<Map<String, Object>> queryPageData(String dataSQL, int page, int pageSize, Map<String, Object> params) {
        if (pageSize<1) {
            throw new RuntimeException("pageSize必须大于0");
        }
        String countSQL = pageSql.getCountSql(dataSQL);
        long totalCount = namedJdbcTemplate.queryForObject(countSQL, params, Long.class);
        List<Map<String, Object>> data = new ArrayList();
        // 起始下标比总数小
        if (totalCount!=0 && (page-1)*pageSize<totalCount) {
            String pageSQL = pageSql.getPageSql(dataSQL, sqlDialect, (page - 1) * pageSize, page * pageSize);
            data = namedJdbcTemplate.queryForList(pageSQL, params)
                    .stream().map(item -> convertKeyToCamel(item))
                    .collect(Collectors.toList());
        }
        return new PageImpl(data, PageRequest.of(page-1, pageSize), totalCount);
    }

    public Map<String, Object> queryForOneMap(String sql, Map<String, ?> paramMap) {
        List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sql, paramMap);
        return ObjectUtils.isEmpty(list)? null:convertKeyToCamel(list.get(0));
    }

    public void insertOne(String tableName, Map<String, Object> fieldValues) {
        String sql = SqlUtils.getInsertOneSql(tableName, fieldValues);
        namedJdbcTemplate.update(sql, new MapSqlParameterSource(fieldValues));
    }

    /**
     * keyType只支持 Integer，Long和String类型
     * @param tableName
     * @param fieldValues
     * @param keyType
     * @param <T>
     * @return
     */
    public Set<Class> keyHolderSet = new HashSet() {{ add(Integer.class); add(Long.class); add(String.class); }};
    public <T> T insertOneReturnPriKey(String tableName, Map<String, Object> fieldValues, Class<T> keyType) {
        if (keyType==null) {
            throw new RuntimeException("keyType not allow null");
        }
        if (!keyHolderSet.contains(keyType)) {
            throw new RuntimeException("keyType only support "+keyHolderSet);
        }
        KeyHolder keyHolder = new GeneratedKeyHolder();// 创建一个主键持有者
        String sql = SqlUtils.getInsertOneSql(tableName, fieldValues);
        namedJdbcTemplate.update(sql, new MapSqlParameterSource(fieldValues), keyHolder);
        Map<String, Object> keys = keyHolder.getKeys();
        for (String key:keys.keySet()) {
            Object obj = keys.get(key);
            return Convert.convert(keyType, obj);
        }
        return null;
    }

    /**
     * 把map的key转换为驼峰命名
     * @param map map对象
     * @return 返回转换后的值
     */
    private static Map<String, Object> convertKeyToCamel(Map<String, Object> map){
        if(map==null) {
            return null;
        }
        Map linkedHashMap = new LinkedHashMap();
        map.forEach((key, value) -> linkedHashMap.put(convert(key), value));
        return linkedHashMap;
    }

    /**
     * 数据库查出来的字段，下划线转驼峰
     * @param defaultName
     * @return
     */
    private static String convert(String defaultName) {
        if (defaultName.contains("_")) {
            char[] arr = defaultName.toCharArray();
            StringBuilder nameToReturn = new StringBuilder();
            for (int i = 0; i < arr.length; i++) {
                if (arr[i] == '_') {
                    nameToReturn.append(Character.toUpperCase(arr[++i]));
                } else {
                    nameToReturn.append(arr[i]);
                }
            }
            return nameToReturn.toString();
        } else {
            return defaultName;
        }
    }

}
